﻿CREATE PROCEDURE [dbo].[sp_Get_ZipCode]
	@MdNumber varchar(4),
	@Month int,
	@Year int
AS
	select zi.ZipCode,
	max(zi.Longitude) as Longitude,
	max(zi.Latitude) as Latitude,
	sum(zi.ActiveLeadCount) as ActiveLeadCount,
	sum(zi.LapsedLeadCount) as LapsedLeadCount,
	sum(zi.NeighborLeadCount) as NeighborLeadCount,
	sum(zi.BusinesLeadCount) as BusinesLeadCount,
	zi.Territory, (case when (count(zi.ZipID) = count(oz.ZipID)) then 1 else 0 end) as IsOrdered,
	max(zi.AgentName)
	from dbo.ZipInfo zi
	left join 
	(select oz.ZipID from Orders o inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
	and o.PeroidMonth = @Month and o.PeroidYear = @Year and o.AccountID = @MdNumber) oz 
	on zi.ZipID = oz.ZipID
	where zi.MdNumber = @MdNumber
	group by zi.ZipCode, zi.Territory